
import openpyxl
from openpyxl import Workbook 
import re
import os

path = "G:/读书笔记/君莫投资课程/code/白酒营业总收入.xlsx"
current_path = os.getcwd()
PROJECT_PATH = os.path.dirname(__file__)

class Report():

    def __init__(self) -> None:
        self.gross_profit_margin = []
        self.filter_codes = []
        self.total_operating_revenue_file = os.path.join(current_path, '白酒营业总收入.xlsx')
        print(self.total_operating_revenue_file)
        self.operating_costs_file = os.path.join(current_path, '白酒营业成本.xlsx')

    def read_xlsx_data(self, path):
        print("======*****************======")
        workbook = openpyxl.load_workbook(path)
        sheet = workbook.active
        cell_value = sheet['A1'].value
        
        row1_values = [cell.value for cell in sheet[1]]
 
        print(row1_values)
        print("===========================")
        column1_values = []
        for i in range(1, sheet.max_row + 1):
            # print(repr(sheet[f'A{i}'].value))
            A_value = sheet[f'A{i}'].value
            if A_value and re.search(r".*\d+.*", A_value):
                if A_value not in self.filter_codes:
                    column1_values.append(sheet[f'A{i}'].value)

        for column in column1_values:  
            print(column)

        print(len(column1_values))
        
        print("===========================")  
        # 遍历所有单元格数据  
        iter_rows = sheet.iter_rows(values_only=True)
        for row in iter_rows:  
            print(row)

        return row1_values, column1_values, iter_rows


    def wirte_result(self, datalist):
        workbook = Workbook()
        save_file = os.path.join(PROJECT_PATH, '白酒指标.xlsx')
        worksheet = workbook.active
        # 每个workbook创建后，默认会存在一个worksheet，对默认的worksheet进行重命名
        worksheet.title = "毛利率"
        for row in datalist:
            worksheet.append(row)
        workbook.save(filename=save_file)

    def get_gross_profit_margin(self):
        row1, column1, data_list1 = self.read_xlsx_data(self.total_operating_revenue_file)
        row2, column2, data_list2 = self.read_xlsx_data(self.operating_costs_file)
        
        title = self.check_consistency(row1, row2)
        # 初始化结果列表
        list3 = []
        list3.append(title)
        # 遍历data_list1和list2的每一行
        for i in range(len(data_list1)):
            # 如果当前行是标题行或数据为空，则跳过
            if (data_list1[i][0] is None) or ('证券代码' in data_list1[i][0]) or ("数据来源" in data_list1[i][0]):
                continue
            
            # 创建新的行，包含编号和名称
            new_row = [data_list1[i][0], data_list1[i][1]]
            
            # 遍历data_list1和list2的每一列（除第一列和第二列外）
            for j in range(2, len(data_list1[i])):
                # 如果两行的公司名称不匹配，则跳过
                if data_list1[i][1] != data_list2[i][1]:
                    break
                
                # 计算营业成本和总收入的百分比
                if data_list1[i][j] is not None and data_list2[i][j] is not None:
                    new_row.append(round((data_list2[i][j] / data_list1[i][j]) * 100, 2))
                else:
                    new_row.append(None)
            
            # 将新的行添加到结果列表中
            list3.append(new_row)

        # 打印结果列表
        print("+++++++++++++++++++++++++++++++++++++++++++")
        for row in list3:
            print(row)
            
        return list3

    def check_consistency(self, list1, list2):
        if len(list1) != len(list2):
            print("Error: 两个数据长度不一致")
            return 
        
        if list1[0] != list2[0] or list1[1] != list2[1]:
            print("Error: 两个数据名称不一致")
            return

        result = [list1[0], list1[1]]
        
        for i in range(2, len(list1)):
            temp1_i = list1[i].replace("\n", "")
            temp2_i = list2[i].replace("\n", "")
            year1, unit1 = self.tilte_match(temp1_i)
            year2, unit2 = self.tilte_match(temp2_i)
                
            if year1 != year2:
                print(f"Error: 年份不一致：{temp1_i} and {temp2_i}.")
                return False

            if unit1 != unit2:
                print(f"Error: 计算单位不一致 {temp1_i} and {temp2_i}.")
                return False
            
            # 添加到结果列表中
            result.append(year1 + unit1)
        # print(result)
        return result

    def tilte_match(self, strpara):
        print("###########################")
        reg = r"(\d{4}年).*单位\](.*元)"
        match1 = re.search(reg, strpara, re.DOTALL)
        if match1:
            year = match1.group(1)
            unit = match1.group(2)
            print(year, unit)
            return year, unit
        else:
            print("名称字段匹配失败")


if __name__ == "__main__":
    report = Report()
    datalist = report.get_gross_profit_margin()
    report.wirte_result(datalist)


      